package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.WordDRGsReportApplication;
import com.dhcc.DRGsHosp.infra.util.Arith;
/**
 * 
  * @Description:分析报告
  * @Author:ShaoZhiYuan
  * @Date:2017年5月27日 下午4:07:21 
  * @copyright:DHC 2017-2018
 */
@Named
@Transactional 
public class WordDRGsReportApplicationImpl implements WordDRGsReportApplication{
	
	private EntityRepository getRepository() {
		EntityRepository repository = null;
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
	/**
	 * 
	  * @Description:看日期区间内是否有数据  
	  * @param dataFrom
	  * @param dataTo
	  * @param drgVersion
	  * @return
	 */
	@Override
	public int findDataIsNull(String dataFrom, String dataTo ,String groupScheme,String hospCode) {
		StringBuffer sql= new StringBuffer();
		String dateFrom=dataFrom+" 00:00:00";
		String dateTo=dataTo+" 23:59:59";
		sql.append(" SELECT COUNT(p.PARID) FROM drgs.MR_IPINFO mr   \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" LEFT JOIN   drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID \n");
		sql.append(" WHERE 1=1 "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append(" and  p.ver_region='"+groupScheme+"' and  instr('"+hospCode+"',BA.p00010001)>0  \n");
		Object cnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		return Integer.valueOf(cnt.toString());
	}
	/**
	 * 
	  * @Description:获取新增病组  
	  * @param dateFrom
	  * @param dateTo
	  * @param benchmarkYear
	  * @param drgVersion
	  * @return
	 */
	@Override
	public List<Object[]> findNewDrgs(String dateFrom, String dateTo, String stdId ,String groupScheme,String hospCode) {
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("stdId",stdId );
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  tp.PARDRG_CODE,COUNT(tp.PARDRG_CODE ) FROM  \n");
		sql.append("(select mr.PMAININDEX ID from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=MR.PMAININDEX  WHERE instr('"+hospCode+"',BA.p00010001)>0 and  mr.P00320000 <61  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" ) tmr \n");
		sql.append(" JOIN (select p.PARID,p.PARDRG_CODE from drgs.t_patientdrg p LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=p.PARID where instr(:hospCode , BA.p00010001)>0 and  p.ver_region=:groupScheme and  p.PARDRG_CODE is NOT NULL and  NOT EXISTS \n");
		sql.append("(SELECT AS_DRG_CODE FROM drgs.t_annualstd ad WHERE p.PARDRG_CODE=ad.as_drg_code  AND ad.as_weight is not null and ad.AS_ID=:stdId))tp  \n");
		sql.append(" ON tmr.ID=tp.PARID  \n");
		sql.append(" GROUP BY tp.PARDRG_CODE \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取出院病历术，大于60.如组数， 入组率  
	  * @param paramMap
	  * @return
	 */
	public Object[] findSimpHospBaseQuota(Map<String,Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获得分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		
		StringBuffer sql= new StringBuffer();
		sql.append(" select X.allcnt ,X.ingroup,X.over60,ROUND(decode(X.allcnt-X.over60,0,0,X.ingroup*100/(X.allcnt-X.over60)),2) ,X.notingroup \n");
		sql.append(" FROM ( select COUNT(mr.PMAININDEX) allcnt,SUM(CASE WHEN mr.P00320000 > 60 THEN 1 else 0 end  ) over60, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is not NULL and p.ver_region=:groupScheme THEN 1 else 0 END ) ingroup, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is  NULL and p.ver_region=:groupScheme  THEN 1 else 0 END ) notingroup \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.pmainindex left join drgs.t_patientdrg p on mr.PMAININDEX= p.PARID  \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" ) X \n");
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString()).setParameters(map).singleResult();
		return hospBaseQuota;
	}
	/**
	 * 
	  * @Description:获取科室数量    
	  * @return
	 */
	@Override
	public int findDepCount() {
		StringBuilder sql = new StringBuilder();	
		sql.append(" SELECT COUNT(1) FROM drgs.t_department \n");	
		Object cnt = getRepository().createSqlQuery(sql.toString()).singleResult();
	    int	 count = Integer.valueOf(cnt.toString());
		return count;
	}
	/**
	 * 
	  * @Description:获取当前系统中可用标杆  
	  * @return
	 */
	@Override
	public List<Object[]> getBenchmarkYear() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT DISTINCT(a.AS_YEAR) FROM drgs.t_annualstd a ORDER BY a.AS_YEAR DESC \n ");	
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取分组器版本号    
	  * @param paramMap
	  * @return
	 */
	@Override
	public String getDRGversion(Map<String, Object> paramMap) {
		String drgsVersionDesc="";
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("drgVersion",drgVersion );
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT VER_DESC,VER_CODE FROM drgs.t_drgversion v WHERE v.VER_CODE= :drgVersion \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		if(list.size()>0){
			Object[] obj=list.get(0);
			drgsVersionDesc=obj[0].toString();
		}
		return drgsVersionDesc;
	}
	/**
	 * 
	  * @Description: 表格二中出院病例数，次均费用，药占比等 
	  * @param paramMap
	  * @return
	 */
	public Object[] findSimpHospBaseTable2(Map<String,Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("reportDateFrom",reportDateFrom+" 00:00:00" );
		map.put("reportDateTo", reportDateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT a.allcnt ALLCNTTWO,ROUND(decode(a.allcnt,0,0,a.COSTAMOUNT/a.allcnt),2) hospavgfee,ROUND(decode(a.allcnt,0,0,a.dayscnt/a.allcnt),2) hospavgdays, \n");
		sql.append(" ROUND(decode(a.COSTAMOUNT,0,0,a.DRUGCOST*100/a.COSTAMOUNT),2) hospdrugratio, ROUND(decode(a.allcnt,0,0,a.CASEDEAD*100/a.allcnt),2) hospdeadratio FROM ( \n");
		sql.append(" SELECT COUNT(mr.PMAININDEX) allcnt ,SUM(mr.P00320000) dayscnt ,sum(mr.P00800000) COSTAMOUNT,\n");
		sql.append(" sum(exp.DRUG_COST) DRUGCOST ,SUM(CASE WHEN mr.P00620001 = '5' THEN 1 ELSE 0 END) CASEDEAD \n");
		sql.append(" FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  left join drgs.MR_EXPENSE_SUM exp on mr.PMAININDEX=exp.PMAININDEX where  \n");
		sql.append(" instr(:hospCode , BA.p00010001)>0  "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+")  a\n");
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString()).setParameters(map).singleResult();
		return hospBaseQuota;
	}
	/**
	 * 
	  * @Description:表格四中drg组数，cmi，总权重   
	  * @param paramMap
	  * @return
	 */
	public Object[] findSimpHospBaseTable4(Map<String,Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("reportDateFrom",reportDateFrom+" 00:00:00" );
		map.put("reportDateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql= new StringBuffer();
		sql.append("  SELECT COUNT(DISTINCT(p.PARDRG_CODE)),ROUND(decode(COUNT(p.PARID),0,0,SUM(a.AS_WEIGHT)/COUNT(p.PARID)),2) ,SUM(a.AS_WEIGHT)totalweight,\n");
		sql.append("  COUNT(p.PARID) casecnt ,round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_FEE,0,0,mr.P00800000/a.AS_AVG_FEE))/COUNT(p.parid)),2) as effee, \n");
		sql.append("  round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_DAYS,0,0,mr.P00320000/a.AS_AVG_DAYS))/COUNT(p.parid)),2) as timefee, \n");
		sql.append("  case when sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END) =0 then 0 else (round(sum(case when mr.P00620001='5' and a.AS_RISK_LEVEL='1' then 1 else 0 END)/sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END)*100,2))end  \n");
		sql.append("  FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  LEFT JOIN  drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID LEFT JOIN   drgs.t_annualstd a ON p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append("  WHERE instr(:hospCode, BA.p00010001)>0 and p.ver_region=:groupScheme and p.PARDRG_CODE is not null AND a.AS_ID=:stdId and mr.P00320000 <61  and a.as_weight is not null  \n");
		sql.append("  "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+" \n");	
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString()).setParameters(map).singleResult();
		return hospBaseQuota;
	}
	/**
	 * 
	 * @methodname    :findHospCaseOperationCount
	 * @description   :获取全院手术病例总数 ,判断isOper是否区分，如果不区分则手动改为1（全为手术）
	 * @param         :Map<String,Object> paramMap
	 * @author        :liqingyang
	 * @return        :String
	 * @date          :2016年3月29日
	 */
	public String findHospCaseOperationCount(Map<String,Object> paramMap){
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("reportDateFrom",reportDateFrom+" 00:00:00" );
		map.put("reportDateTo", reportDateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		String sql = "SELECT COUNT(A.PMAININDEX)  FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX  JOIN DRGS.MR_OPERATION A ON A.PMAININDEX = B.PMAININDEX WHERE instr(:hospCode , BA.p00010001)>0 and A.PO1400 in ('0','1') and  A.POTYPE='M' "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","B",2);
		String cnt = getRepository().createSqlQuery(sql).setParameters(map).singleResult().toString();
		if(Integer.parseInt(cnt)<=1){
			   StringBuilder sqlUpdate=new StringBuilder(); 
			   sqlUpdate.append(" UPDATE drgs.MR_OPERATION op set op.PO1400='1' \n ");
	    	   getRepository().createSqlQuery(sqlUpdate.toString()).executeUpdate();
		}
		String sqlResult = "SELECT COUNT(A.PMAININDEX)  FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX JOIN DRGS.MR_OPERATION A ON A.PMAININDEX = B.PMAININDEX WHERE instr(:hospCode , BA.p00010001)>0 and A.PO1400 ='1'  and  A.POTYPE='M' "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","B",2);
		String caseOperationCount = getRepository().createSqlQuery(sqlResult).setParameters(map).singleResult().toString();
		return caseOperationCount;
	}
	/**
	 * 
	  * @Description: 获取手术级别信息   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findOperCaseByLvel(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT OP.PO0301,COUNT(OP.PMAININDEX) \n");
		sql.append(" FROM drgs.MR_IPINFO mr ,drgs.MR_OPERATION OP,DRGS.MR_BASEINFO  BA \n");
	    sql.append(" WHERE op.PO1400 ='1' and mr.PMAININDEX=OP.PMAININDEX and OP.POTYPE='M' and BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
		sql.append(" GROUP BY OP.PO0301 ORDER BY COUNT(OP.PMAININDEX) DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:查询手术级别信息   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Map<String, Object>> findOperLevelInfo(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> parmap= new HashMap<String, Object>();
		parmap.put("reportDateFrom",reportDateFrom+" 00:00:00" );
		parmap.put("reportDateTo", reportDateTo+" 23:59:59");
		parmap.put("stdId", stdId);
		parmap.put("groupScheme", groupScheme);
		parmap.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.oplevel, a.outCaeCnt,b.inCaseCnt,b.cmi,c.notinCaeCnt from ( \n");
		sql.append(" SELECT (CASE WHEN o.PO0301 is null THEN '无' else o.PO0301 end) as oplevel,COUNT(mr.PMAININDEX) outCaeCnt FROM drgs.MR_IPINFO mr ,drgs.MR_OPERATION o ,DRGS.MR_BASEINFO  BA  \n");
		sql.append("  WHERE BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and o.PO1400 ='1' and mr.PMAININDEX=o.PMAININDEX AND o.POTYPE='M'  "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+"  GROUP BY o.PO0301)a LEFT JOIN \n");
		sql.append(" (select (CASE WHEN o.PO0301 is null THEN '无' else o.PO0301 end) as oplevel,count(p.PARID) inCaseCnt, ROUND(decode(count(p.PARID),0,0,SUM(a.AS_WEIGHT)/count(p.PARID)),2) cmi  FROM drgs.MR_IPINFO mr , \n");
		sql.append("   DRGS.MR_BASEINFO  BA, drgs.t_patientdrg  p ,drgs.MR_OPERATION o,drgs.t_annualstd a \n");
		sql.append("  WHERE BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and p.ver_region=:groupScheme and mr.PMAININDEX=p.PARID   AND p.PARDRG_CODE=a.AS_DRG_CODE   "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+"   and p.PARDRG_CODE is not null  AND mr.P00320000 <61 \n");
		sql.append(" AND mr.PMAININDEX=o.PMAININDEX  AND a.AS_ID=:stdId  AND o.PO1400 ='1' and o.POTYPE='M' GROUP BY o.PO0301 \n");
		sql.append(" ) b ON a.oplevel=b.oplevel LEFT JOIN \n");
		sql.append(" ( SELECT (CASE WHEN o.PO0301 is null THEN '无' else o.PO0301 end) as oplevel,COUNT(mr.PMAININDEX) notinCaeCnt FROM drgs.MR_IPINFO mr ,DRGS.MR_BASEINFO  BA,drgs.MR_OPERATION o ,drgs.t_patientdrg pd \n");
		sql.append(" WHERE BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and pd.ver_region=:groupScheme and mr.PMAININDEX=o.PMAININDEX and mr.PMAININDEX=pd.PARID AND o.PO1400 ='1' and o.POTYPE='M'  and pd.PARDRG_CODE is NULL AND mr.P00320000 <61 \n");
		sql.append("  "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+" \n");
		sql.append("  GROUP BY o.PO0301 )c ON b.oplevel=c.oplevel \n");
		sql.append(" ORDER BY a.oplevel ASC\n");
		StringBuilder sqlHosp = new StringBuilder();
		sqlHosp.append(" SELECT a.outCaeCnt,b.inCaseCnt,b.cmi,c.notinCaeCnt from (\n");
		sqlHosp.append(" SELECT COUNT(O.PMAININDEX) outCaeCnt FROM drgs.MR_IPINFO mr , drgs.MR_OPERATION o , DRGS.MR_BASEINFO  BA\n");
		sqlHosp.append("  WHERE BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and mr.PMAININDEX=o.PMAININDEX AND o.PO1400 ='1' and o.POTYPE='M'  \n");
		sqlHosp.append("   "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+" )a, \n");
		sqlHosp.append(" (SELECT count(p.PARID) inCaseCnt, ROUND(decode(count(p.PARID),0,0,SUM(a.AS_WEIGHT)/count(p.PARID)),2) cmi  FROM drgs.MR_IPINFO mr , DRGS.MR_BASEINFO  BA ,\n");
		sqlHosp.append(" drgs.t_patientdrg  p ,drgs.MR_OPERATION o,drgs.t_annualstd a \n");
		sqlHosp.append(" WHERE BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and p.ver_region=:groupScheme and mr.PMAININDEX=p.PARID   AND p.PARDRG_CODE=a.AS_DRG_CODE AND p.PARDRG_CODE is not null   \n");
		sqlHosp.append("  AND mr.PMAININDEX=o.PMAININDEX  AND  a.AS_ID=:stdId AND o.PO1400 ='1' and o.POTYPE='M' AND MR.P00320000 <61 \n");
		sqlHosp.append("  "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+" ) b ,\n");
		sqlHosp.append("(  SELECT COUNT(mr.PMAININDEX) notinCaeCnt FROM drgs.MR_IPINFO mr ,drgs.MR_OPERATION o ,drgs.t_patientdrg pd,DRGS.MR_BASEINFO  BA   \n");
		sqlHosp.append("   WHERE BA.PMAININDEX=MR.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and pd.ver_region=:groupScheme and mr.PMAININDEX=o.PMAININDEX and mr.PMAININDEX=pd.PARID and o.PO1400 ='1'  AND o.POTYPE='M'  "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+" and pd.PARDRG_CODE is NULL AND mr.P00320000 <61  \n");
		sqlHosp.append("    "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","mr",2)+"   )c\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(parmap).list();
		Object[] hospData = getRepository().createSqlQuery(sqlHosp.toString()).setParameters(parmap).singleResult();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();		
		//全院手术
		Map<String, Object> hspmap = new HashMap<String, Object>();
		hspmap.put("operLevel", "全院手术");
		hspmap.put("caseCnt", hospData[0]);
		hspmap.put("caseCntRatio", 100);
		hspmap.put("inGroupCnt", hospData[1]);
		hspmap.put("CMI", hospData[2]);
		hspmap.put("notInGroupCnt", hospData[3]);
		resultList.add(hspmap);
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			String obj0=obj[0]==null?"0":obj[0].toString().trim();
			Double caseCntRatio=Arith.div(Double.valueOf(obj[1]+"")*100,Double.valueOf(hospData[0]+""),2);;
		   if("1".equals(obj0)){
				map.put("operLevel", "一级");
				map.put("caseCnt", obj[1]==null?"0": obj[1].toString().trim());
				map.put("caseCntRatio", caseCntRatio+"");
				map.put("inGroupCnt", obj[2]==null?"0": obj[2].toString().trim());
				map.put("CMI", obj[3]==null?"0": obj[3].toString().trim());
				map.put("notInGroupCnt", obj[4]==null?"0": obj[4].toString().trim());
		   }else  if("2".equals(obj0)){
				map.put("operLevel", "二级"); 
				map.put("caseCnt", obj[1]==null?"0": obj[1].toString().trim());
				map.put("caseCntRatio", caseCntRatio+"");
				map.put("inGroupCnt", obj[2]==null?"0": obj[2].toString().trim());
				map.put("CMI", obj[3]==null?"0": obj[3].toString().trim());
				map.put("notInGroupCnt", obj[4]==null?"0": obj[4].toString().trim());
		   }else  if("3".equals(obj0)){
			   map.put("operLevel", "三级"); 
				map.put("caseCnt", obj[1]==null?"0": obj[1].toString().trim());
				map.put("caseCntRatio", caseCntRatio+"");
				map.put("inGroupCnt", obj[2]==null?"0": obj[2].toString().trim());
				map.put("CMI", obj[3]==null?"0": obj[3].toString().trim());
				map.put("notInGroupCnt", obj[4]==null?"0": obj[4].toString().trim());
		   }else  if("4".equals(obj0)){
			   map.put("operLevel", "四级"); 
				map.put("caseCnt", obj[1]==null?"0": obj[1].toString().trim());
				map.put("caseCntRatio", caseCntRatio+"");
				map.put("inGroupCnt", obj[2]==null?"0": obj[2].toString().trim());
				map.put("CMI", obj[3]==null?"0": obj[3].toString().trim());
				map.put("notInGroupCnt", obj[4]==null?"0": obj[4].toString().trim());
		   }else{
			   map.put("operLevel", "其他"); 
				map.put("caseCnt", obj[1]==null?"0": obj[1].toString().trim());
				map.put("caseCntRatio",caseCntRatio+"");
				map.put("inGroupCnt", obj[2]==null?"0": obj[2].toString().trim());
				map.put("CMI", obj[3]==null?"0": obj[3].toString().trim());
				map.put("notInGroupCnt", obj[4]==null?"0": obj[4].toString().trim());
		   }		 
		
			resultList.add(map);
		}	
		return resultList;
	}
	/**
	 * 
	  * @Description:获取主要手术个数种类   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findMainOperList(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();    
		sql.append(" SELECT o.PO0101,COUNT(m.PMAININDEX) cnt  FROM drgs.MR_OPERATION o \n ");	
		sql.append("  LEFT  JOIN drgs.MR_IPINFO m  ON o.PMAININDEX=m.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX  \n ");	
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and  o.PO1400 ='1' and o.POTYPE='M' \n ");
		sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","m",2)+" ");	
		sql.append("GROUP BY o.PO0101  ORDER BY cnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	
/*	*//**
	 * 
	  * @Description:计算前20的主要手术的病例里哪两个手术最多   
	  * @param paramMap
	  * @return
	 *//*
	@Override
	public List<Object[]> findMainOperLevelList(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		StringBuilder sql = new StringBuilder(); 
		
		sql.append(" select Y.oper_level, Y.cnty \n ");
		sql.append(" from  (select PO0301 oper_level, count(1) cnty  \n ");
		sql.append(" from drgs.MR_OPERATION op LEFT JOIN drgs.MR_IPINFO mr on mr.PMAININDEX=op.PMAININDEX where 1=1   \n ");
		sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" ");	
		sql.append(" and op.POTYPE='M' and op.PO0101 in (select X.code from (SELECT  o.PO0101 code , count(1) cnt \n ");
	 	sql.append(" from drgs.MR_OPERATION o LEFT JOIN drgs.MR_IPINFO mr on mr.PMAININDEX=o.PMAININDEX \n ");
	 	sql.append("where o.PO1400='1' and  o.POTYPE='M' \n ");
		sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" ");	
		sql.append(" GROUP BY o.PO0101  ORDER BY cnt desc  )X where  rownum<=20  \n ");
		sql.append(" ) group by op.PO0301 ORDER BY cnty desc ) Y  \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}*/
	/**
	 * 
	  * @Description:计算前20的主要手术的病例里哪两个手术最多   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findMainOperLevelList(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		Map<String, Object> mapnew= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder(); 
		
		sql.append(" select X.code, X.cnt from (SELECT  o.PO0101 code ,count(1) cnt \n ");
		sql.append(" from drgs.MR_OPERATION o LEFT JOIN drgs.MR_IPINFO mr on mr.PMAININDEX=o.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n ");
	 	sql.append("where instr(:hospCode , BA.p00010001)>0 and  o.PO1400='1' and  o.POTYPE='M' \n ");
		sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" ");	
		sql.append(" GROUP BY o.PO0101  ORDER BY count(1) desc  )X where  rownum<=20  \n ");
		
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		String operStr="";
		if(list.size()>0){
			for (int i = 0; i < list.size(); i++) {
				Object[] obj=list.get(i);
				String  str=obj[0]==null?"":obj[0]+"";
				operStr+="'"+str+"',";
			}
		}
		if (operStr.lastIndexOf(",") > 0) {
			operStr= operStr.substring(0, operStr.lastIndexOf(","))+"";
		}
		StringBuilder sqlnew = new StringBuilder(); 
		sqlnew.append(" select Y.oper_level, Y.cnty \n ");
		sqlnew.append(" from  (select op.PO0301 oper_level, count(1) cnty  \n ");
		sqlnew.append(" from drgs.MR_OPERATION op where op.PO1400='1' and  op.POTYPE='M' and op.PO0101 in ("+operStr+" ) \n ");
		sqlnew.append(" group by op.PO0301 ORDER BY cnty desc ) Y  \n ");
		
		List<Object[]> listnew = getRepository().createSqlQuery(sqlnew.toString()).setParameters(mapnew).list();
		return listnew;
	}
	/**
	 * 
	  * @Description: 获取前五位的手术编码   
	  * @param paramMap
	  * @return
	 */
	@Override
	public 	List<Object[]>  findTop5OperCode(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append("  select X.PO0101, X.cnt from ( \n");
		sql.append(" SELECT B.PO0101 ,COUNT(b.PMAININDEX) as cnt FROM drgs.MR_IPINFO a LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX   \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and  b.PO1400 ='1' and b.POTYPE='M' \n");
	    sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","a",2)+" \n");
		sql.append(" GROUP BY B.PO0101 ORDER BY cnt DESC ) X  where rownum<=5 \n");	
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:第5的手术编码科室分布   
	  * @param paramMap
	  * @param operCode
	  * @return
	 */
	@Override
	public List<Map<String, Object>> top5OpercodedepListByOperCode(Map<String, Object> paramMap, Object operCode) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT b.PO0101,b.PO0100,b.PO0301,a.P00300000,COUNT(a.PMAININDEX) as cnt \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.MR_OPERATION  b ON a.PMAININDEX=b.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.POTYPE='M' AND b.PO0101='"+operCode+"' \n");
	    sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+" \n");
		sql.append(" GROUP BY  b.PO0101,b.PO0100,b.PO0301, a.P00300000 ORDER BY cnt DESC \n");	
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		sql = new StringBuilder();
		sql.append(" SELECT count(b.PMAININDEX) as cnt \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.MR_OPERATION  b ON a.PMAININDEX=b.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.POTYPE='M' AND b.PO0101='"+operCode+"' \n");
	    sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+" \n");
		Object totalcnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		int othDepopercnt=0;
		int depcnt=0;
		int circleOrder=0;
		if(!list.isEmpty()){
		for(int i=0;i<list.size();i++){
			Object[] obj =list.get(i);
			Object[] objo=list.get(i);
			if(i>0){
			 objo =list.get(i-1);	
			}
			Map<String, Object> map=new HashMap<String, Object>();
			if(obj[0]!=objo[0]){
				depcnt++;//解决同一个手术不同级别问题
			}
			if(depcnt<3){
				map.put("order", i+1);
				circleOrder++;
				map.put("opercode", obj[0]==null?"--":obj[0].toString().trim());
				map.put("operation", obj[1]==null?"--":obj[1].toString().trim());
				map.put("operlevel", obj[2]==null?"--":obj[2].toString().trim());
				map.put("depname", obj[3]==null?"--":obj[3].toString().trim());
				map.put("opercnt", obj[4]==null?0:obj[4].toString().trim());
				if(Double.valueOf(totalcnt==null?"0":totalcnt+"").intValue()!=0){
					map.put("operdepratio",Arith.mul( Arith.div(Double.valueOf(obj[4]==null?"0":obj[4]+""), Double.valueOf(totalcnt==null?"0":totalcnt+""),4), 100));
				}else{
					map.put("operdepratio",0);
				}
				mapList.add(map);
			}else{
				othDepopercnt+=Double.valueOf(obj[4]==null?"0":obj[4]+"");	
			}
		}
	} 
		    Object[] othobj =list.get(0);
		    String othDeoOperCode=othobj[0]==null?"":othobj[0]+"";
		    String othDeoOperName=othobj[1]==null?"":othobj[1]+"";
			Map<String, Object> map=new HashMap<String, Object>();
	//		map.put("order", operOrder);
			map.put("order", ++circleOrder);
			map.put("opercode",othDeoOperCode);
			map.put("operation", othDeoOperName);
			map.put("operlevel","--");
			map.put("depname", "其他");
			map.put("opercnt",othDepopercnt);
			map.put("operdepratio",Arith.mul( Arith.div(othDepopercnt, Double.valueOf(totalcnt==null?"0":totalcnt+""),4), 100));
			mapList.add(map);
			return mapList;
	}
	/**
	 * 
	  * @Description: 一级手术前5手术编码，名称，频次，占本级手术的占比 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findmainOperLevelInfo(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();	
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT b.PO0301, COUNT(B.PMAININDEX) CNT FROM drgs.MR_IPINFO a LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n ");	
		sql.append("  WHERE instr(:hospCode , BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' "+Arith.mrOutDateSql("dateFrom","dateTo","a",2)+" \n ");	
		sql.append("  GROUP BY b.PO0301 ORDER BY b.PO0301 ASC \n ");	
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description: 获取各级手术的出现频率最高的手术  
	  * @param paramMap
	  * @param level
	  * @return
	 */
	@Override
	public List<Object[]> findmainOperByLevelInfo(Map<String, Object> paramMap, String level) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		if("4".equals(level)||"3".equals(level)||"2".equals(level)||"1".equals(level)){
			sql.append("  SELECT  B.PO0101, b.PO0100, COUNT(B.PMAININDEX) CNT FROM drgs.MR_IPINFO a LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX \n");
			sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' AND B.PO0301='"+level+"' AND \n");
			sql.append("  1=1 "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+"   \n");
			sql.append("  GROUP BY b.PO0101,B.PO0100 ORDER BY CNT DESC \n");
		}else{
			sql.append("  SELECT  B.PO0101, b.PO0100, COUNT(B.PMAININDEX) CNT FROM drgs.MR_IPINFO a LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX \n");
			sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' AND B.PO0301 not in ('1','2','3','4') AND \n");
			sql.append("  1=1 "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+"  \n");
			sql.append("  GROUP BY b.PO0101,B.PO0100 ORDER BY CNT DESC \n");
		}
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	 
	}
	/**
	 * 
	  * @Description:获取全院DRGs病组   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findHospDRGsInfo(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql= new StringBuffer();
		
		sql.append("	SELECT  (AZ.totaldrg) as alldrgs ,AX.WAIKE,AX.NEIKE,AX.CAOZUO,AY.WAIKEY,AY.NEIKEY,AY.CAOZUOY FROM (  \n ");
		sql.append("	SELECT  SUM(case WHEN SUBSTR(X.drgcode,2,1)='A' OR SUBSTR(X.drgcode,2,1)='B' OR SUBSTR(X.drgcode,2,1)='C' \n ");
		sql.append("	 OR SUBSTR(X.drgcode,2,1)='D' OR SUBSTR(X.drgcode,2,1)='E' OR SUBSTR(X.drgcode,2,1)='F' OR SUBSTR(X.drgcode,2,1)='G' \n ");
		sql.append("	 OR SUBSTR(X.drgcode,2,1)='H' OR SUBSTR(X.drgcode,2,1)='J'  THEN 1 ELSE 0 END) AS WAIKE,  \n ");
		sql.append("	SUM(case WHEN SUBSTR(X.drgcode,2,1)='K' OR SUBSTR(X.drgcode,2,1)='L' OR SUBSTR(X.drgcode,2,1)='M'   \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='N' OR SUBSTR(X.drgcode,2,1)='P' OR SUBSTR(X.drgcode,2,1)='Q'  THEN 1 ELSE 0 END) AS CAOZUO,  \n ");
		sql.append("	SUM(case WHEN SUBSTR(X.drgcode,2,1)='R' OR SUBSTR(X.drgcode,2,1)='S' OR SUBSTR(X.drgcode,2,1)='T' OR SUBSTR(X.drgcode,2,1)='U'  \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='V' OR SUBSTR(X.drgcode,2,1)='W' OR SUBSTR(X.drgcode,2,1)='X'  \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='Y' OR SUBSTR(X.drgcode,2,1)='Z'  THEN 1 ELSE 0 END) AS NEIKE  \n ");
		sql.append("	FROM (SELECT DISTINCT p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n ");
		sql.append("	 where instr(:hospCode, BA.p00010001)>0 and  p.PARDRG_CODE is not null and p.ver_region=:groupScheme "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n ");
		sql.append("	)X)AX ,(SELECT SUM(case WHEN SUBSTR(Y.drgcode,2,1)='A' OR SUBSTR(Y.drgcode,2,1)='B' OR SUBSTR(Y.drgcode,2,1)='C' \n ");
		sql.append("	 OR SUBSTR(Y.drgcode,2,1)='D' OR SUBSTR(Y.drgcode,2,1)='E' OR SUBSTR(Y.drgcode,2,1)='F' OR SUBSTR(Y.drgcode,2,1)='G'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='H' OR SUBSTR(Y.drgcode,2,1)='J'  THEN 1 ELSE 0 END) AS WAIKEY, \n ");
		sql.append("	 SUM(case WHEN SUBSTR(Y.drgcode,2,1)='K' OR SUBSTR(Y.drgcode,2,1)='L' OR SUBSTR(Y.drgcode,2,1)='M'  \n ");
		sql.append("      OR SUBSTR(Y.drgcode,2,1)='N' OR SUBSTR(Y.drgcode,2,1)='P' OR SUBSTR(Y.drgcode,2,1)='Q'  THEN 1 ELSE 0 END) AS CAOZUOY, \n ");
		sql.append("	SUM(case WHEN SUBSTR(Y.drgcode,2,1)='R' OR SUBSTR(Y.drgcode,2,1)='S' OR SUBSTR(Y.drgcode,2,1)='T'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='U' OR SUBSTR(Y.drgcode,2,1)='V' OR SUBSTR(Y.drgcode,2,1)='W' OR SUBSTR(Y.drgcode,2,1)='X'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='Y' OR SUBSTR(Y.drgcode,2,1)='Z'  THEN 1 ELSE 0 END) AS NEIKEY \n ");
		sql.append("	FROM (SELECT  p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID \n ");
		sql.append("	 where instr(:hospCode , BA.p00010001)>0 and  p.PARDRG_CODE is not null and p.ver_region=:groupScheme "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" )Y) AY,  \n ");
		sql.append("    (SELECT count(DISTINCT p.PARDRG_CODE) as totaldrg from drgs.MR_IPINFO mr LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX   \n ");
		sql.append("	where instr(:hospCode , BA.p00010001)>0 and  p.PARDRG_CODE is not null and p.ver_region=:groupScheme "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" )AZ  \n ");
		
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取伴合并症的人数    
	  * @param paramMap
	  * @param object
	  * @return
	 */
	@Override
	public List<Object[]> findPersonCnt(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();	
		
		sql.append(" SELECT AX.DRGsType1Num,AX.DRGsType3Num,AX.DRGsType5Num,AY.CaseType1Num,AY.CaseType3Num,AY.CaseType5Num FROM ( \n");
		sql.append(" SELECT SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('1','2') THEN 1 ELSE 0 END) AS DRGsType1Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('3','4')  THEN 1 ELSE 0 END) AS DRGsType3Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('5','6')  THEN 1 ELSE 0 END) AS DRGsType5Num \n");
		sql.append(" FROM ( \n");
		sql.append(" SELECT DISTINCT p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region=:groupScheme "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
		sql.append(" )X \n");
		sql.append(" )AX , \n");
		sql.append(" (SELECT SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('1','2')   THEN 1 ELSE 0 END) AS CaseType1Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('3','4')  THEN 1 ELSE 0 END) AS CaseType3Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('5','6')  THEN 1 ELSE 0 END) AS CaseType5Num \n");
		sql.append(" FROM ( \n");
		sql.append(" SELECT  p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID   \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region=:groupScheme "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+"\n");
		sql.append(" )Y) AY \n");

		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description: 获取当前时间段内科室组的数量 
	  * @param paramMap
	  * @return
	 */
	@Override
	public int findHospNowDepCnt(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom")+" 00:00:00";
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo")+" 23:59:59";
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		StringBuilder sql=new StringBuilder();
		sql.append(" select COUNT(DISTINCT mr.P00300000) from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and  1=1 "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+"");
		Object cnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		int	 count = Integer.valueOf(cnt==null?"0":cnt.toString().trim());
		return count;
	}
	/**
	 * 
	  * @Description:获取科室权重和CMI  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Map<String, Object>> findDepWeightAndCMI(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> Pmap= new HashMap<String, Object>();
		Pmap.put("reportDateFrom",reportDateFrom+" 00:00:00" );
		Pmap.put("reportDateTo", reportDateTo+" 23:59:59");
		Pmap.put("stdId", stdId);
		Pmap.put("groupScheme", groupScheme);
		Pmap.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT M.P00300000, ROUND(SUM(A.AS_WEIGHT),2),ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 ) \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=M.PMAININDEX LEFT JOIN DRGS.T_PATIENTDRG P ON M .PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A ON P.PARDRG_CODE = A.AS_DRG_CODE   \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and  1=1 "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","M",2)+" \n");
		sql.append(" AND a.AS_ID=:stdId AND A.AS_WEIGHT  IS not NULL and m.P00320000 <61 and p.ver_region=:groupScheme and P.PARDRG_CODE is not null GROUP BY M.P00300000 \n");		
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(Pmap).list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("depname", obj[0]==null?"--":obj[0].toString());
			map.put("weight", obj[1]==null?0:obj[1].toString());
			map.put("cmi", obj[2]==null?0:obj[2].toString());
			resultList.add(map);
		}
		return resultList;
	}
	/**
	 * 
	  * @Description: 获取科室的时间费用消耗指数 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findEfficiencyDepInfoList(Map<String,Object> paramMap){
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT E.P00300001,E.P00300000,round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_FEE,0,0,E.P00800000/G.AS_AVG_FEE))/COUNT(G.parid)),2), \n");
		sql.append(" round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_DAYS,0,0,E.P00320000/G.AS_AVG_DAYS))/COUNT(G.parid)),2)  \n");
		sql.append(" FROM  DRGS.MR_IPINFO E RIGHT JOIN (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_ID=:stdId \n");
		sql.append(" AND EXISTS (SELECT B.PMAININDEX FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX  WHERE instr(:hospCode , BA.p00010001)>0 and A.PARID = B.PMAININDEX  AND A.PARDRG_CODE IS NOT NULL  \n");
		sql.append(" AND B.P00320000 <61 and A.ver_region=:groupScheme  "+Arith.mrOutDateSql("dateFrom","dateTo","B",2)+" \n");
		sql.append(" )) G  ON E.PMAININDEX = G.PARID GROUP BY E.P00300001,E.P00300000 \n");
		sql.append(" ORDER BY round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_FEE,0,0,E.P00800000/G.AS_AVG_FEE))/COUNT(G.parid)),2) DESC, round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_DAYS,0,0,E.P00320000/G.AS_AVG_DAYS))/COUNT(G.parid)),2)  DESC\n");
		List<Object[]> efficiencyDepInfoList = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return efficiencyDepInfoList;
	}
	/**
	 * 
	 * findHospCaseRisk1Cnt.  
	 * Description:获取低风险死亡病例数
	 * author LiuHongjun
	 * @param paramMap
	 * @return 
	 * Date:2017年1月12日下午3:44:29
	 */
	public String findHospCaseRisk1Cnt(Map<String,Object> paramMap){
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
	//	String benchmarkYear = paramMap == null?"":paramMap.get("benchmarkYear").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("datefrom",reportDateFrom+" 00:00:00" );
		map.put("dateto", reportDateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
	//	map.put("benchmarkYear", benchmarkYear);
		map.put("groupScheme", groupScheme);
		map.put("stdId", stdId);
		map.put("hospCode", hospCode);
		StringBuffer sql =new StringBuffer();
		sql.append(" select Risk1DeadCaseCnt \n");
		sql.append(" from ( select \n");
		sql.append(" sum(case when a.P00620001='5' and c.as_risk_level=1 then 1 else 0 end) as Risk1DeadCaseCnt \n");
		sql.append(" from DRGs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  left join DRGs.t_patientdrg b on a.PMAININDEX=b.parid and b.ver_region=:groupScheme \n");
		sql.append(" left join drgs.t_drgs drg on drg.DRG_CODE=b.PARDRG_CODE and drg.VER_CODE=:drgVersion \n");
		sql.append(" left join drgs.t_adrg adrg on adrg.ADRG_CODE=drg.ADRG_CODE and drg.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.MDC_CODE=adrg.MDC_CODE and mdc.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join DRGs.t_department dep on a.P00300001=dep.dep_code \n");
		sql.append(" left join DRGs.t_Depgroup depgroup on depgroup.depgroup_code=dep.pardepgroup_code \n");
		sql.append(" left join DRGs.t_annualstd c on b.pardrg_code=c.as_drg_code and c.AS_ID=:stdId   left join DRGs.t_medins d on d.parid=a.PMAININDEX \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 "+Arith.mrOutDateSql("datefrom","dateto","a",2)+") a  \n");
		Object obj = getRepository().createSqlQuery(sql.toString()).setParameters(map).singleResult();
		String hospCaseRisk1Cnt=obj==null?"0":obj.toString();
		return hospCaseRisk1Cnt;
	}
	/**
	 * 
	  * @Description:获取低风险死亡人数各科室分布情况表  
	  * @param paramMap
	  * @return
	 */
	public List<Object[]> findQualityDepTableByRisk1List(Map<String,Object> paramMap){
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("reportDateFrom",reportDateFrom+" 00:00:00");
		map.put("reportDateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		sql.append(" SELECT a.dep,a.incnt,a.deadcnt,a.ratio FROM (  \n");
		sql.append(" SELECT a.P00300000 dep ,sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='1' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='1'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID and b.ver_region=:groupScheme \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE  \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and a.P00320000 <61 and b.PARDRG_CODE is not NULL   \n");
		sql.append(" and c.AS_ID=:stdId AND 1=1 "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","a",2)+"   \n");
		sql.append(" GROUP BY a.P00300000 ORDER BY deadcnt DESC   \n");
		sql.append(" )a WHERE a.incnt <>0 AND a.ratio >0  \n");
		List<Object[]> qualityDepTableByRisk1List = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return qualityDepTableByRisk1List;
	}
	/**
	 * 
	 * findHospCaseRisk2Cnt.  
	 * Description:获取中低风险死亡病例数
	 * author LiuHongjun
	 * @param paramMap
	 * @return 
	 * Date:2017年1月12日下午3:44:29
	 */
	public String findHospCaseRisk2Cnt(Map<String,Object> paramMap){
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("datefrom",reportDateFrom+" 00:00:00" );
		map.put("dateto", reportDateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql =new StringBuffer();
		sql.append(" select Risk2DeadCaseCnt \n");
		sql.append(" from ( select \n");
		sql.append(" sum(case when a.P00620001='5' and c.as_risk_level=2 then 1 else 0 end) as Risk2DeadCaseCnt \n");
		sql.append(" from DRGs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  left join DRGs.t_patientdrg b on a.PMAININDEX=b.parid and b.ver_region=:groupScheme \n");
		sql.append(" left join drgs.t_drgs drg on drg.DRG_CODE=b.PARDRG_CODE and drg.VER_CODE=:drgVersion \n");
		sql.append(" left join drgs.t_adrg adrg on adrg.ADRG_CODE=drg.ADRG_CODE and drg.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.MDC_CODE=adrg.MDC_CODE and mdc.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join DRGs.t_department dep on a.P00300001=dep.dep_code \n");
		sql.append(" left join DRGs.t_Depgroup depgroup on depgroup.depgroup_code=dep.pardepgroup_code \n");
		sql.append(" left join DRGs.t_annualstd c on b.pardrg_code=c.as_drg_code and c.AS_ID=:stdId   left join DRGs.t_medins d on d.parid=a.PMAININDEX \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 "+Arith.mrOutDateSql("datefrom","dateto","a",2)+") a  \n");
		Object obj = getRepository().createSqlQuery(sql.toString()).setParameters(map).singleResult();
		String hospCaseRisk2Cnt=obj==null?"0":obj.toString();
		return hospCaseRisk2Cnt;
	}
	/**
	 * 
	  * @Description:获取中低风险死亡人数各科室分布情况表  
	  * @param paramMap
	  * @return
	 */
	public List<Object[]> findQualityDepTableByRisk2List(Map<String,Object> paramMap){
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("reportDateFrom",reportDateFrom+" 00:00:00");
		map.put("reportDateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		sql.append(" SELECT a.dep,a.incnt,a.deadcnt,a.ratio FROM (  \n");
		sql.append(" SELECT a.P00300000 dep ,sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='2' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='2'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID and b.ver_region=:groupScheme  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE  \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and a.P00320000 <61 and b.PARDRG_CODE is not NULL   \n");
		sql.append(" and c.AS_ID=:stdId AND 1=1 "+Arith.mrOutDateSql("reportDateFrom","reportDateTo","a",2)+"   \n");
		sql.append(" GROUP BY a.P00300000 ORDER BY deadcnt DESC   \n");
		sql.append(" )a WHERE a.incnt <>0 AND a.ratio >0  \n");
		List<Object[]> qualityDepTableByRisk2List = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return qualityDepTableByRisk2List;
	}
	/**
	 * 
	  * @Description:获取MDC总数 
	  * @param paramMap
	  * @return
	 */
	@Override
	public int findMdcCnt(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT COUNT(DISTINCT b.MDC) from  \n");
		sql.append(" drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and a.P00320000 <61 and b.PARDRG_CODE is not NULL and b.ver_region=:groupScheme "+Arith.mrOutDateSql("dateFrom","dateTo","a",2)+"  \n");
		Object  mdcCnt = getRepository().createSqlQuery(sql.toString()).setParameters(map).singleResult();
		int mdcCntint=Integer.valueOf(mdcCnt==null?"0":mdcCnt+"");
		return mdcCntint;
	}
	/**
	 * 
	  * @Description:获取缺失的MDC组  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findOldLostMDCList(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" select  mg.MDC_CODE ,mg.MDC_DESC  from drgs.t_mdcgroup mg where mg.VER_CODE= :drgVersion \n");
		sql.append(" and NOT EXISTS( \n");
		sql.append(" select distinct pg.mdc from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg on mr.PMAININDEX=pg.parid  \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and pg.MDC=mg.MDC_CODE AND  pg.ver_region=:groupScheme and pg.PARDRG_CODE is not NULL and mr.P00320000 <61 \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" ) \n");
		List<Object[]> OldLostMDCList = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return OldLostMDCList;
	}
	/**
	 * 
	  * @Description:DRGs病组覆盖率最高的前五个MDC专业
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findOldHighRiskAboveMDCList(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" select X.fmdc, X.fdesc, X.drgsRatio from (\n");
		sql.append(" select e.mdc fmdc,g.mdc_desc fdesc,(round(decode(g.drgTotalCount,0,0,e.drgsCount/g.drgTotalCount),4))*100 drgsRatio from  \n");
		sql.append("(SELECT a.mdc,count(distinct(a.pardrg_code)) drgsCount,a.ver_code FROM DRGS.T_PATIENTDRG a WHERE  a.ver_region=:groupScheme and \n");
		sql.append(" a.pardrg_code is not null and exists ( \n");
		sql.append(" SELECT B.PMAININDEX FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX   WHERE instr(:hospCode , BA.p00010001)>0 and A.PARID = B.PMAININDEX   "+Arith.mrOutDateSql("dateFrom","dateTo","B",2)+" \n");
		sql.append(" ) group by a.mdc, a.ver_code) e  \n");
		sql.append(" left join ( select mg.mdc_code, count(drg.drg_code) drgTotalCount,mg.ver_code,mg.mdc_desc \n");
		sql.append(" from DRGs.t_Drgs drg, DRGs.t_Adrg adrg, DRGs.t_Mdcgroup mg \n");
		sql.append(" WHERE drg.adrg_code = adrg.adrg_code \n");
		sql.append(" and adrg.mdc_code = mg.mdc_code \n");
		sql.append(" and mg.ver_code = drg.ver_code  and drg.ver_code=adrg.ver_code \n");
		sql.append(" group by mg.mdc_code,mg.ver_code,mg.mdc_desc \n");
		sql.append(" ) g on e.mdc = g.mdc_code and e.ver_code=g.ver_code order by  drgsRatio desc )X  where rownum<=5  \n");
		List<Object[]> OldLostMDCList = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return OldLostMDCList;
	}
	/**
	 * 
	  * @Description:高难度高产出的MDC专业   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findMdcCW(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT e.as_mdc_code, e.mdc_desc, e.mdcmi,e.weight  \n");
		sql.append("  FROM (SELECT t.as_mdc_code, m.mdc_desc, decode(count(A.PARID),0,0,sum(T.AS_WEIGHT)/count(A.PARID)) mdcmi,sum(T.AS_WEIGHT) weight \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T, DRGs.T_MDCGROUP m  \n");
		sql.append("  WHERE A.ver_region=:groupScheme  and A.PARDRG_CODE = T.AS_DRG_CODE  AND T.AS_ID=:stdId \n");
		sql.append(" and m.ver_code=:drgVersion AND A.PARDRG_CODE is NOT NULL and t.as_mdc_code = m.mdc_code   AND EXISTS \n");
		sql.append(" (SELECT B.PMAININDEX  FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX   WHERE instr(:hospCode , BA.p00010001)>0 and A.PARID = B.PMAININDEX AND B.P00320000 <61 \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","B",2)+")\n");
		sql.append(" group by t.as_mdc_code,m.mdc_desc) e  order by e.mdcmi desc \n");
		List<Object[]> mdcTableInfoByCMIList = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return mdcTableInfoByCMIList;
	}
	/**
	 * 
	  * @Description:获取各MDC下费用消耗指数和时间消耗指数  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findFeeAndTimeEff(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT G.MDC,G.MDC_DESC,round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_FEE,0,0,E.P00800000/G.AS_AVG_FEE))/COUNT(G.parid)),2), \n");
		sql.append(" round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_DAYS,0,0,E.P00320000/G.AS_AVG_DAYS))/COUNT(G.parid)),2) TIMEEFFe \n");
		sql.append(" FROM DRGS.MR_IPINFO E \n");
		sql.append(" RIGHT JOIN (SELECT A.PARID, A.PARDRG_CODE, T.AS_AVG_FEE, T.AS_AVG_DAYS,A.MDC,MC.MDC_DESC \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T,DRGS.T_MDCGROUP MC \n");
		sql.append(" WHERE A.ver_region=:groupScheme and A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_ID=:stdId AND A.MDC = MC.MDC_CODE AND MC.VER_CODE=:drgVersion \n");
		sql.append(" AND EXISTS (SELECT B.PMAININDEX FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=B.PMAININDEX WHERE instr(:hospCode , BA.p00010001)>0 and A.PARID = B.PMAININDEX AND B.P00320000 <61 \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","B",2)+")) G \n");
		sql.append(" ON E.PMAININDEX = G.PARID  group by G.MDC,G.MDC_DESC ORDER BY TIMEEFFe DESC  \n");
		List<Object[]> feeAndTimeEffList = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return feeAndTimeEffList;
	}
	/**
	 * 
	 * @Description :获取mdc低风险排序  
	 * @methodname :mdcFTRisk1List
	 * @author:ShaoZhiYuan
	 * @return List<Object[]>
	 * @date :2017年2月21日
	 */
	@Override
	public List<Object[]> findmdcFTRisk1List(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.mdc,a.MDC_DESC,a.deadcnt,(decode(a.incnt,0,0,a.deadcnt/a.incnt))*100 FROM (  \n");
		sql.append(" SELECT b.mdc,c.MDC_DESC,sum(case when d.AS_RISK_LEVEL='1' then 1 else 0 end) as incnt, \n");
		sql.append(" sum(case when d.AS_RISK_LEVEL='1' AND a.P00620001='5' then 1 else 0 end) as deadcnt \n");
		sql.append(" FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID and b.ver_region=:groupScheme \n");
		sql.append(" LEFT JOIN drgs.t_mdcgroup c ON b.MDC=c.MDC_CODE and c.VER_CODE=:drgVersion \n");
		sql.append(" LEFT JOIN drgs.t_annualstd d on b.PARDRG_CODE=d.AS_DRG_CODE \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and b.PARDRG_CODE is not NULL \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","a",2)+" \n");
		sql.append(" AND d.AS_ID=:stdId   GROUP BY b.MDC,c.MDC_DESC \n");
		sql.append(" )a WHERE a.deadcnt<>0  ORDER BY a.deadcnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	 * @Description :获取mdc中低风险排序  
	 * @methodname :mdcFTRisk2List
	 * @author:ShaoZhiYuan
	 * @return List<Object[]>
	 * @date :2017年2月21日
	 */
	@Override
	public List<Object[]> findmdcFTRisk2List(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.mdc,a.MDC_DESC,a.deadcnt, (decode(a.incnt,0,0,a.deadcnt/a.incnt))*100 FROM (  \n");
		sql.append(" SELECT b.mdc,c.MDC_DESC,sum(case when d.AS_RISK_LEVEL='2' then 1 else 0 end) as incnt, \n");
		sql.append(" sum(case when d.AS_RISK_LEVEL='2' AND a.P00620001='5' then 1 else 0 end) as deadcnt \n");
		sql.append(" FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID \n");
		sql.append(" LEFT JOIN drgs.t_mdcgroup c ON b.MDC=c.MDC_CODE \n");
		sql.append(" LEFT JOIN drgs.t_annualstd d on b.PARDRG_CODE=d.AS_DRG_CODE \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and c.VER_CODE=:drgVersion AND b.ver_region=:groupScheme AND b.PARDRG_CODE is not NULL \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","a",2)+" \n");
		sql.append(" AND d.AS_ID=:stdId   GROUP BY b.MDC,c.MDC_DESC \n");
		sql.append(" )a WHERE a.deadcnt<>0  ORDER BY a.deadcnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description: 一二级手术例数最多的前十名医师 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDocOper1List(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		sql.append(" select X.fopdc, X.fdep , X.fcnt , X.fscnt , X.fraio , X.fincnt , X.fcmi  from ( \n");
		sql.append(" select a.opdc fopdc  ,a.P00300000 fdep,a.allcnt fcnt,a.sumcnt fscnt,ROUND(a.sumcnt/a.allcnt,4)*100 fraio,b.incnt fincnt, b.cmi fcmi from ( \n");
		sql.append(" SELECT op.PO0400 as opdc ,COUNT(op.PO0400) as allcnt ,mr.P00300000 , \n");
		sql.append(" SUM(CASE WHEN op.PO0301='1' OR op.PO0301='2' THEN 1 else 0 end) as sumcnt  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
		sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and op.PO0400 is not null  and op.PO1400='1' and  op.POTYPE='M' \n");
		sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
		sql.append(" GROUP BY op.PO0400,mr.P00300000   \n");
		sql.append(" )a LEFT JOIN (SELECT   op.PO0400 as opdcb , \n");
		sql.append(" sum(case when pg.PARDRG_CODE is not null THEN 1 else 0 end ) as incnt , \n");
		sql.append(" ROUND(sum(ad.AS_WEIGHT)/COUNT(op.PO0400),2)  as cmi  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
	    sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX  LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg    on pg.PARID=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd ad     on ad.AS_DRG_CODE=pg.PARDRG_CODE \n");
	    sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and op.PO0400 is not null and pg.ver_region=:groupScheme  and op.PO1400='1' and  op.POTYPE='M'  and ad.AS_ID=:stdId \n");
	    sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
	    sql.append(" GROUP BY op.PO0400  )b on a.opdc=b.opdcb  ORDER BY a.sumcnt DESC )X where rownum<=10  \n");
		
		
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description: 三四级手术例数最多的前十名医师 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDocOper2List(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00");
		map.put("dateTo", dateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuilder sql = new StringBuilder();
		
		sql.append(" select X.fopdc, X.fdep , X.fcnt , X.fscnt , X.fraio , X.fincnt , X.fcmi  from ( \n");
		sql.append(" select a.opdc fopdc  ,a.P00300000 fdep,a.allcnt fcnt,a.sumcnt fscnt,ROUND(a.sumcnt/a.allcnt,4)*100 fraio,b.incnt fincnt, b.cmi fcmi from ( \n");
		sql.append(" SELECT op.PO0400 as opdc ,COUNT(op.PO0400) as allcnt ,mr.P00300000 , \n");
		sql.append(" SUM(CASE WHEN op.PO0301='3' OR op.PO0301='4' THEN 1 else 0 end) as sumcnt  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
		sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX   \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and op.PO0400 is not null  and op.PO1400='1' and  op.POTYPE='M' \n");
		sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
		sql.append(" GROUP BY op.PO0400,mr.P00300000   \n");
		sql.append(" )a LEFT JOIN (SELECT   op.PO0400 as opdcb , \n");
		sql.append(" sum(case when pg.PARDRG_CODE is not null THEN 1 else 0 end ) as incnt , \n");
		sql.append(" ROUND(sum(ad.AS_WEIGHT)/COUNT(op.PO0400),2)  as cmi  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
	    sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX   \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg    on pg.PARID=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd ad     on ad.AS_DRG_CODE=pg.PARDRG_CODE \n");
	    sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and op.PO0400 is not null and pg.ver_region=:groupScheme  and op.PO1400='1' and  op.POTYPE='M'  and ad.AS_ID=:stdId \n");
	    sql.append("  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
	    sql.append(" GROUP BY op.PO0400  )b on a.opdc=b.opdcb  ORDER BY a.sumcnt DESC )X where rownum<=10  \n");
	    
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取重点专科名字  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findMdcDesc(Map<String, Object> paramMap) {
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT  DISTINCT  a.MDC_CODE,a.MDC_DESC FROM drgs.t_mdcgroup a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.VER_CODE=b.VER_CODE  \n");
		sql.append(" WHERE b.ver_region='"+groupScheme+"' and a.MDC_CODE in ("+mdcCode+") \n");
		List<Object[]> mdclist = getRepository().createSqlQuery(sql.toString()).list();
		return mdclist;
	}
	/**
	 * 
	  * @Description:获取重点专科病例数  
	  * @param paramMap
	  * @return
	 */
	public int findMdcCaseCnt(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00";
		String dateTo = dateto+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT COUNT(b.PARID) FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX   \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0  and a.P00320000<61 AND b.PARDRG_CODE is not NULL and b.ver_region='"+groupScheme+"' \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+" \n");
		sql.append(" and b.MDC in ("+mdcCode+") \n");
		Object casecnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		return Integer.parseInt(casecnt==null?"0":casecnt+"");
	}
	/**
	 * 
	  * @Description:获取重点专科drg组数  
	  * @param paramMap
	  * @return
	 */
	@Override
	public int findMdcDRGsCnt(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00";
		String dateTo = dateto+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT(b.PARDRG_CODE)) FROM drgs.MR_IPINFO a  LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0  and  a.P00320000<61 AND b.PARDRG_CODE is not NULL and b.ver_region='"+groupScheme+"'\n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+"  \n");
		sql.append(" and b.MDC in ("+mdcCode+") \n");
		Object casecnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		return Integer.parseInt(casecnt==null?"0":casecnt+"");
	}
   /**
    * 
    * @Description: 获取重点专科实际drg组数   
    * @param paramMap
    * @return
    */
	@Override
	public int findMdcDRGsRealCnt(Map<String, Object> paramMap) {
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT A.DRG_CODE) FROM drgs.t_drgs a WHERE CONCAT('MDC',substr(a.DRG_CODE,length(1)-1,1)) in ("+mdcCode+")  AND a.VER_CODE=(SELECT DISTINCT b.VER_CODE FROM drgs.t_patientdrg b where b.ver_code='"+drgVersion+"' )  \n");
		Object drgscnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		return Integer.parseInt(drgscnt==null?"0":drgscnt+"");
	}
	/**
	 * 
	  * @Description:重点专业下DRGS病组分析   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Map<String, Object>> findmdcDdrgtop10MapList(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		int casecnt=Integer.valueOf(paramMap == null?"0":paramMap.get("mdcCasecnt")+"");
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00";
		String dateTo = dateto+" 23:59:59";
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT B.PARDRG_CODE,D.DRG_DESC,count(B.PARID) AS CNT,C.AS_WEIGHT FROM drgs.MR_IPINFO A  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX LEFT JOIN drgs.t_patientdrg B ON A.PMAININDEX=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and B.ver_region='"+groupScheme+"' and  C.AS_ID='"+stdId+"' AND A.P00320000 <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"A",1)+"  \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in ("+mdcCode+") \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY CNT DESC   \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		int i=1;
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		for(Object[] obj:list){
			Map<String, Object> map = new HashMap<String, Object>();
			if(i<=10){
			map.put("order", i);
			map.put("drgcode", obj[0]+"");
			//生成的DRG相关数据做大小于号的相关判断处理，防止打开报xml架构无效的bug
			if((obj[1]+"").contains("<")){
				map.put("drgdesc", (obj[1]+"").replace("<", "小于"));
			}else if((obj[1]+"").contains(">")){
				map.put("drgdesc", (obj[1]+"").replace(">", "大于"));
			}else{
				map.put("drgdesc", obj[1]+"");
			}
			map.put("outcase", obj[2]+"");
			if(casecnt!=0){
				map.put("caseratio", Arith.mul(Arith.div(Double.valueOf(obj[2]==null?"0":obj[2]+""),Double.valueOf(casecnt+""), 4),100 ));
			}else{
				map.put("caseratio",0);
			}	
			map.put("weight", obj[3]+"");
			
			mapList.add(map);}
			i++;
		}	
		return mapList;
	}
	/**
	 * 
	 * @Description:重点专业下DRGS权重分析   
	 * @param paramMap
	 * @return
	 */
	@Override
	public List<Map<String, Object>> findmdcdrgweighttop10List(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00";
		String dateTo = dateto+" 23:59:59";
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT sum(C.AS_WEIGHT) FROM drgs.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX   \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.PMAININDEX=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and B.ver_region='"+groupScheme+"' and  C.AS_ID='"+stdId+"' AND A.P00320000 <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"A",1)+" \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in ("+mdcCode+") \n");
		Object mdctotalweight = getRepository().createSqlQuery(sql.toString()).singleResult();
		double mdctotalweightd=Double.valueOf(mdctotalweight==null?"0":mdctotalweight+"");
		sql = new StringBuilder();
		sql.append(" SELECT B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT weight ,count(B.PARID) AS CNT,count(B.PARID)*C.AS_WEIGHT totalweight FROM drgs.MR_IPINFO A  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX LEFT JOIN drgs.t_patientdrg B ON A.PMAININDEX=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and B.ver_region='"+groupScheme+"' and C.AS_ID='"+stdId+"' AND A.P00320000 <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"A",1)+"   \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in ("+mdcCode+") \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY totalweight DESC  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		int i=1;
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		for(Object[] obj:list){
			Map<String, Object> map = new HashMap<String, Object>();
			if(i<=10){
			map.put("order", i);
			map.put("drgcode", obj[0]+"");
			//生成的DRG相关数据做大小于号的相关判断处理，防止打开报xml架构无效的bug
			if((obj[1]+"").contains("<")){
				map.put("drgdesc", (obj[1]+"").replace("<", "小于"));
			}else if((obj[1]+"").contains(">")){
				map.put("drgdesc", (obj[1]+"").replace(">", "大于"));
			}else{
				map.put("drgdesc", obj[1]+"");
			}
			map.put("weight", obj[2]+"");
			map.put("outcase", obj[3]+"");
			map.put("totalweight", obj[4]+"");
			map.put("mdctotalweight", mdctotalweight);
			
			if(mdctotalweightd!=0){
				map.put("weightratio", Arith.mul(Arith.div(Double.valueOf(obj[4]==null?"0":obj[4]+""),mdctotalweightd, 4),100 ));
			}else{
				map.put("weightratio",0);
			}	
			
			mapList.add(map);
			}i++;
		}	
		return mapList;
	}
	
	/**
	 * 
	 * @Description:计算重点mdc专业下总权重    
	 * @param paramMap
	 * @return
	 */
	@Override
	public String findmdctotalweightcount(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00";
		String dateTo = dateto+" 23:59:59";
		StringBuilder sql = new StringBuilder();
		
		sql.append(" SELECT sum(C.AS_WEIGHT) FROM drgs.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX   \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.PMAININDEX=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0  and B.ver_region='"+groupScheme+"' and  C.AS_ID='"+stdId+"' AND A.P00320000 <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"A",1)+" \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in ("+mdcCode+") \n");
		Object mdctotalweight = getRepository().createSqlQuery(sql.toString()).singleResult();
		double mdctotalweightd=Double.valueOf(mdctotalweight==null?"0":mdctotalweight+"");
		return mdctotalweightd+"";
	}
	/**
	 * 
	 * @Description: 表格27计算 
	 * @param paramMap
	 * @return
	 */
	@Override
	public Object[] findlistMDCbyTable27(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String mdcCode = paramMap == null?"":paramMap.get("mdcCode").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00"; 
		String dateTo = dateto+" 23:59:59";
		StringBuffer sql =new StringBuffer();
		sql.append("  SELECT COUNT(DISTINCT(p.PARDRG_CODE)),ROUND(decode(COUNT(p.PARID),0,0,SUM(a.AS_WEIGHT)/COUNT(p.PARID)),2) ,\n");
		sql.append("  round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_FEE,0,0,mr.P00800000/a.AS_AVG_FEE))/COUNT(p.parid)),2) as effee,  \n");
		sql.append("  round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_DAYS,0,0,mr.P00320000/a.AS_AVG_DAYS))/COUNT(p.parid)),2) as timefee,  \n");
		sql.append("  round(decode(sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END),0,0,sum(case when mr.P00620001='5' and a.AS_RISK_LEVEL='1' then 1 else 0 END)/sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END))*100,2), \n");
		sql.append("  round(decode(sum(case WHEN a.AS_RISK_LEVEL='2' then 1 else 0 END),0,0,sum(case when mr.P00620001='5' and a.AS_RISK_LEVEL='2' then 1 else 0 END)/sum(case WHEN a.AS_RISK_LEVEL='2' then 1 else 0 END))*100,2)  \n");
		sql.append("  FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  LEFT JOIN  drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID LEFT JOIN   drgs.t_annualstd a ON p.PARDRG_CODE=a.AS_DRG_CODE  \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND a.AS_ID='"+stdId+"' and mr.P00320000 <61  and a.as_weight is not null  \n");
		sql.append("  "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append("  and P.MDC IN ("+mdcCode+") \n");
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString()).singleResult();
		return hospBaseQuota;
	}
	/**
	 * 
	  * @Description:未入组原因分析  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findNotInGroupReason(Map<String, Object> paramMap) {
		String datefrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String dateto = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = datefrom+" 00:00:00"; 
		String dateTo = dateto+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT g.NOTIN_REASON ,COUNT(G.MR_ADM) FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_groupinglog g ON mr.PMAININDEX=g.MR_ADM \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID\n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and mr.P00320000<61 "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append(" and p.PARDRG_CODE is NULL AND  g.NOTIN_REASON not in ('0') GROUP BY g.NOTIN_REASON \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取大于60天的病例在科室中的分布  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDepOver60List(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT mr.P00300000,COUNT(mr.PMAININDEX) casecnt FROM drgs.MR_IPINFO mr \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX WHERE instr('"+hospCode+"',BA.p00010001)>0 and mr.P00320000 >60 \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append(" GROUP BY mr.P00300000 ORDER BY casecnt desc \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description: 获取未入组病例的科室分布 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findNotInGroupCaseByDepList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT mr.P00300000,COUNT(mr.PMAININDEX) notInCaeCnt FROM drgs.MR_IPINFO mr  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and mr.P00320000<61 "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append(" and  p.PARDRG_CODE IS NULL GROUP BY mr.P00300000 ORDER BY notInCaeCnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取入组病例的诊断信息  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findNotInGroupCaseByDiagList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00";
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" select X.DIAGCODE, X.DIAGDESC, X.notInCaeCnt from ( \n");
		sql.append(" SELECT dg.PD0101 DIAGCODE ,dg.PD0100 DIAGDESC ,COUNT(mr.PMAININDEX) notInCaeCnt FROM drgs.MR_IPINFO mr  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID \n");
		sql.append(" LEFT JOIN drgs.MR_DIAGNOSE dg ON mr.PMAININDEX=dg.PMAININDEX \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and dg.PDTYPE='M' and p.ver_region='"+groupScheme+"' and mr.P00320000<61 "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append(" and  p.PARDRG_CODE IS NULL AND dg.PD0101 IS NOT NULL  GROUP BY dg.PD0101,dg.PD0100  ORDER BY notInCaeCnt DESC )X WHERE rownum<=5  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取未入组病例的手术信息  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findNotInGroupCaseByOperList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" select X.opercode, X.operdesc, X.notInCaeCnt from (\n");
		sql.append(" SELECT o.PO0101 opercode ,o.PO0100 operdesc ,COUNT(mr.PMAININDEX) notInCaeCnt FROM drgs.MR_IPINFO mr  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID \n");
		sql.append(" LEFT JOIN drgs.MR_OPERATION o ON mr.PMAININDEX=o.PMAININDEX \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0  and p.ver_region='"+groupScheme+"' and mr.P00320000<61 "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n");
		sql.append(" and  p.PARDRG_CODE IS NULL AND o.POTYPE='M'  AND o.PO0101 IS NOT NULL  GROUP BY o.PO0101,o.PO0100  ORDER BY notInCaeCnt DESC ) X WHERE rownum<=5  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取mdc下drg组数前五的mdc专业  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findMdcDrgsTop5List(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append("select Z.fmdc, Z.RATIO, Z.fdesc from ( \n");
		sql.append("SELECT x.MDC fmdc, round(decode(y.CNT,0,0,x.CNT/y.CNT),2) as ratio ,x.MDC_DESC fdesc FROM (  \n");
		sql.append("select p.MDC ,count(DISTINCT p.PARDRG_CODE) AS CNT ,mp.MDC_DESC FROM  drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append("LEFT JOIN drgs.t_patientdrg p on p.PARID=mr.PMAININDEX LEFT JOIN drgs.t_mdcgroup mp on mp.MDC_CODE=p.MDC   \n");
		sql.append("where instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not NULL and mp.VER_CODE='"+drgVersion+"'  \n");
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+"  \n");
		sql.append("GROUP BY p.MDC,mp.MDC_DESC ) x LEFT JOIN  \n");
		sql.append("(SELECT CONCAT('MDC',SUBSTR(A.DRG_CODE,1,1)) AS MDC ,COUNT(DISTINCT A.DRG_CODE) AS CNT  \n");
		sql.append("FROM drgs.t_drgs A WHERE A.VER_CODE='"+drgVersion+"'  \n");
		sql.append("GROUP BY CONCAT('MDC',SUBSTR(A.DRG_CODE,1,1)))y  \n");
		sql.append("ON x.MDC=y.MDC ORDER BY ratio ASC ) Z where rownum<=5  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取mdc专业权重大于1的mdc  
	  * @param paramMap
	  * @param mdcCodeLast5
	  * @return
	 */
	@Override
	public List<Object[]> findMdcDrgsByWeightOver1List(Map<String, Object> paramMap, String mdcCodeLast5) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		
		sql.append(" select X.FMDC, X.fdrg, X.fdesc, X.fweight from (\n");
		sql.append("SELECT CONCAT('MDC',SUBSTR(a.DRG_CODE,1,1)) fmdc,a.DRG_CODE fdrg,a.DRG_DESC fdesc,b.AS_WEIGHT fweight FROM drgs.t_drgs a,drgs.t_annualstd b \n"); 
		sql.append("WHERE a.VER_CODE='"+drgVersion+"' \n");
		sql.append("AND a.DRG_CODE=b.AS_DRG_CODE AND b.AS_ID='"+stdId+"' AND b.AS_WEIGHT >1 AND CONCAT('MDC',SUBSTR(a.DRG_CODE,1,1)) IN ("+mdcCodeLast5+") \n");
		sql.append(" AND a.DRG_CODE NOT IN(  \n");
		sql.append("SELECT P.PARDRG_CODE FROM drgs.MR_IPINFO mr ,DRGS.MR_BASEINFO  BA ,drgs.t_patientdrg p  \n");
		sql.append("WHERE BA.PMAININDEX=MR.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARID=mr.PMAININDEX  and P.MDC in ("+mdcCodeLast5+") \n");
		sql.append(""+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+"  \n");
		sql.append(") ORDER BY b.AS_WEIGHT DESC )X where rownum<=20 \n");	
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取mdc专业权重大于1的mdc的个数 
	  * @param paramMap
	  * @param mdcCodeLast5
	  * @return
	 */
	@Override
	public int findMdcDrgsByWeightOver1Cnt(Map<String, Object> paramMap, String mdcCodeLast5) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		
		sql.append(" SELECT count(1) FROM drgs.t_drgs a,drgs.t_annualstd b  \n");
		sql.append("WHERE a.VER_CODE='"+drgVersion+"' \n");
		sql.append("AND a.DRG_CODE=b.AS_DRG_CODE AND b.AS_ID='"+stdId+"' AND b.AS_WEIGHT >1 AND CONCAT('MDC',SUBSTR(a.DRG_CODE,1,1)) IN ("+mdcCodeLast5+") \n");
		sql.append(" AND a.DRG_CODE NOT IN(  \n");
		sql.append("SELECT P.PARDRG_CODE FROM drgs.MR_IPINFO mr ,DRGS.MR_BASEINFO  BA ,drgs.t_patientdrg p  \n");
		sql.append("WHERE BA.PMAININDEX=MR.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARID=mr.PMAININDEX  and P.MDC in ("+mdcCodeLast5+") \n");
		sql.append(""+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+"  ) \n");
		Object cntObj = getRepository().createSqlQuery(sql.toString()).singleResult();
		return cntObj==null?0: Double.valueOf(cntObj.toString()).intValue();
	}
	/**
	 * 
	  * @Description:获取mdc的权重大于9的mdc  
	  * @param paramMap
	  * @param mdcCodeLast5
	  * @return
	 */
	@Override
	public List<Object[]> findMdcDrgsByWeightOver9List(Map<String, Object> paramMap, String mdcCodeLast5) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" select X.FMDC, X.fdrg, X.fdesc, X.fweight from (\n");
		sql.append("SELECT CONCAT('MDC',SUBSTR(a.DRG_CODE,1,1)) fmdc,a.DRG_CODE fdrg,a.DRG_DESC fdesc,b.AS_WEIGHT fweight FROM drgs.t_drgs a,drgs.t_annualstd b \n"); 
		sql.append("WHERE a.VER_CODE='"+drgVersion+"' \n");
		sql.append("AND a.DRG_CODE=b.AS_DRG_CODE AND b.AS_ID='"+stdId+"' AND b.AS_WEIGHT >9 AND CONCAT('MDC',SUBSTR(a.DRG_CODE,1,1)) IN ("+mdcCodeLast5+") \n");
		sql.append(" AND a.DRG_CODE NOT IN(  \n");
		sql.append("SELECT P.PARDRG_CODE FROM  drgs.MR_IPINFO mr ,DRGS.MR_BASEINFO  BA, drgs.t_patientdrg p  \n");
		sql.append("WHERE BA.PMAININDEX=MR.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARID=mr.PMAININDEX  and P.MDC in ("+mdcCodeLast5+") \n");
		sql.append(""+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+"  \n");
		sql.append(") ORDER BY b.AS_WEIGHT DESC )X where rownum<=20 \n");	
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description:获取drg的费用消耗指数  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDrgsFeeList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" 	SELECT * FROM (\n");
		sql.append(" 	SELECT b.PARDRG_CODE,d.DRG_DESC,decode(COUNT(b.PARID),0,0,SUM(decode(c.AS_AVG_FEE,0,0,a.P00800000/c.AS_AVG_FEE))/COUNT(b.PARID)) as efffee  \n");
		sql.append(" 	FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n");
		sql.append(" 	LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID \n");
		sql.append(" 	LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE \n");
		sql.append("  	LEFT JOIN drgs.t_drgs d ON c.AS_DRG_CODE=d.DRG_CODE \n");
		sql.append(" 	WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.ver_region='"+groupScheme+"' and  b.VER_CODE=d.VER_CODE and a.P00320000 < 61  \n");
		sql.append("    AND  b.PARDRG_CODE is not NULL AND c.AS_ID='"+stdId+"' AND c.AS_WEIGHT is not null \n");
		sql.append("    "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+" \n");
		sql.append("  	GROUP BY b.PARDRG_CODE,d.DRG_DESC  )m WHERE m.efffee >1 ORDER BY m.efffee DESC\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}	  
	/**
	 * 
	  * @Description:获取drg的时间消耗指数   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDrgsTimeList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		String dateFrom = reportDateFrom+" 00:00:00"; 
		String dateTo = reportDateTo+" 23:59:59";
		StringBuffer sql= new StringBuffer();
		sql.append(" 	SELECT * FROM (\n");
		sql.append(" 	SELECT b.PARDRG_CODE,d.DRG_DESC,decode(COUNT(b.PARID),0,0,SUM(decode(c.AS_AVG_DAYS,0,0,a.P00320000/c.AS_AVG_DAYS))/COUNT(b.PARID)) as timefee  \n");
		sql.append(" 	FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n");
		sql.append(" 	LEFT JOIN drgs.t_patientdrg b on a.PMAININDEX=b.PARID \n");
		sql.append(" 	LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE \n");
		sql.append("  	LEFT JOIN drgs.t_drgs d ON c.AS_DRG_CODE=d.DRG_CODE \n");
		sql.append(" 	WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.ver_region='"+groupScheme+"' and b.VER_CODE=d.VER_CODE and a.P00320000 < 61  \n");
		sql.append("    AND b.PARDRG_CODE is not NULL AND c.AS_ID='"+stdId+"' AND c.AS_WEIGHT is not null \n");
		sql.append("    "+Arith.mrOutDateSql(dateFrom,dateTo,"a",1)+" \n");
		sql.append("  	GROUP BY b.PARDRG_CODE,d.DRG_DESC  )m WHERE m.timefee >1 ORDER BY m.timefee DESC\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}
	/**
	 * 
	  * @Description: 附表1 全院医疗服务综合能力情况表 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findHospAbilityList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT COUNT(p.PARID) as depincnt,ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 ) as depcmi, \n");
		sql.append(" COUNT(DISTINCT P.PARDRG_CODE) AS depdrgs ,ROUND(SUM(A.AS_WEIGHT),2) as depweight,  round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,M.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2) AS deptime, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,M.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) as depfee , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as depratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as deprate \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=M.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M .PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and A.AS_ID=:stdId  AND A.AS_WEIGHT  IS not NULL  and p.ver_region=:groupScheme and P.PARDRG_CODE is not null \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","M",2)+" \n");
		sql.append(" ORDER BY depincnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:附表1 科室医疗服务综合能力情况表  
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDepAbilityList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT M.P00300000 as depdesc,COUNT(p.PARID) as depincnt,ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 ) as depcmi, \n");
		sql.append(" COUNT(DISTINCT P.PARDRG_CODE) AS depdrgs ,ROUND(SUM(A.AS_WEIGHT),2) as depweight, \n");
	    sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,M.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2) AS deptime, \n"); 
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,M.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) as depfee , \n"); 
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as depratio, \n"); 
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as deprate  \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=M.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" WHERE instr(:hospCode , BA.p00010001)>0 and A.AS_ID=:stdId  AND A.AS_WEIGHT  IS not NULL  and p.ver_region=:groupScheme  and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","M",2)+" \n");
		sql.append(" GROUP BY m.P00300000 ORDER BY depincnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	 * @Description:附表2 全院DRGs病组覆盖情况指标  
	 * @param paramMap
	 * @return
	 */
	@Override
	public List<Object[]> findHospallDRGsList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT Y.drgTotalCount  ,ROUND(decode(Y.drgTotalCount,0,0,X.drgsCount/Y.drgTotalCount),4)*100  FROM \n");
		sql.append(" ( SELECT COUNT(DISTINCT A.PARDRG_CODE) drgsCount \n");    
		sql.append(" FROM  DRGS.MR_IPINFO B ,DRGS.MR_BASEINFO  BA ,DRGS.T_PATIENTDRG A \n");
		sql.append(" WHERE BA.PMAININDEX=B.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and A.ver_region=:groupScheme  AND A.PARDRG_CODE is NOT NULL  AND  A.PARID = B.PMAININDEX  \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","B",2)+" ) X , \n");
		sql.append(" (select COUNT( ds.DRG_CODE ) drgTotalCount from drgs.t_drgs ds where ds.VER_CODE= :drgVersion ) Y \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description: 附表2 各MDC专业DRGs病组覆盖情况及医疗服务综合能力情况表 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findMDCAbilityList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String drgVersion = paramMap == null?"":paramMap.get("drgVersion").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("drgVersion", drgVersion);
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		
		sql.append(" SELECT  X.MDC ,X.mdc_desc,X.mdcincnt,Y.drgTotalCount ,ROUND(decode(Y.drgTotalCount,0,0,X.drgsCount/Y.drgTotalCount),4)*100 ,X.mdcmi ,X.drgsCount ,X.weight , \n");
		sql.append(" X.mdctime ,X.mdcfee,X.mdcratio,X.mdcrate FROM \n");
		sql.append(" ( SELECT A.MDC, m.mdc_desc,count(A.PARID) mdcincnt, decode(count(A.PARID),0,0,sum(T.AS_WEIGHT)/count(A.PARID)) mdcmi, \n");
		sql.append(" sum(T.AS_WEIGHT) weight ,COUNT(DISTINCT A.PARDRG_CODE) drgsCount , \n");  
		sql.append(" round(decode(COUNT(A.PARID),0,0,SUM(decode(T.AS_AVG_DAYS,0,0,B.P00320000/T.AS_AVG_DAYS))/COUNT(A.PARID)),2) AS mdctime, \n");
		sql.append(" round(decode(COUNT(A.PARID),0,0,SUM(decode(T.AS_AVG_FEE,0,0,B.P00800000/T.AS_AVG_FEE))/COUNT(A.PARID)),2) as mdcfee , \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='1'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as mdcratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='2'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as mdcrate \n");
		sql.append(" FROM  DRGS.T_ANNUALSTD T, DRGs.T_MDCGROUP m  ,DRGS.MR_IPINFO B , DRGS.MR_BASEINFO  BA ,DRGS.T_PATIENTDRG A \n");
		sql.append(" WHERE BA.PMAININDEX=B.PMAININDEX and instr(:hospCode , BA.p00010001)>0 and A.ver_region=:groupScheme and A.PARDRG_CODE = T.AS_DRG_CODE  AND T.AS_ID=:stdId  \n");
		sql.append(" and a.ver_code = m.ver_code AND A.PARDRG_CODE is NOT NULL and t.as_mdc_code = m.mdc_code   AND  A.PARID = B.PMAININDEX AND B.P00320000 <61  \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","B",2)+" \n");
		sql.append(" group by A.MDC, m.mdc_desc ) X LEFT JOIN \n");
		sql.append(" (select mg.mdc_code, count(DISTINCT drg.drg_code) drgTotalCount,mg.ver_code,mg.mdc_desc \n");
		sql.append(" from DRGs.t_Drgs drg, DRGs.t_Adrg adrg, DRGs.t_Mdcgroup mg  \n");
		sql.append(" WHERE drg.adrg_code = adrg.adrg_code \n");
		sql.append(" and adrg.mdc_code = mg.mdc_code \n");
		sql.append(" and adrg.VER_CODE=mg.ver_code and mg.ver_code=drg.ver_code and drg.ver_code=:drgVersion \n");
		sql.append(" group by mg.mdc_code,mg.ver_code,mg.mdc_desc )Y  ON X.MDC=Y.mdc_code \n");
		sql.append(" ORDER BY X.mdcincnt DESC \n");
		
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:附表3住院病例DRGs总权重最高的前二十位主治医师   
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDocByWeightList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		sql.append(" select  DISTINCT mr.P00600300  ,mr.P00300000 ,SUM(A.AS_WEIGHT) weight,COUNT(DISTINCT p.PARDRG_CODE),ROUND(decode(COUNT(P.PARID),0,0,SUM(A.AS_WEIGHT)/COUNT(P.PARID)),2) cmi , \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,mr.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  time, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,mr.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) fee , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 rate, \n");
		sql.append(" count(p.PARID) incnt \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON mr.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and mr.P00600300 IS NOT NULL AND A.AS_ID=:stdId AND A.AS_WEIGHT IS not NULL  and p.ver_region=:groupScheme and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
		sql.append(" GROUP BY mr.P00600300,mr.P00300000  ORDER BY weight DESC  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:附表4CMI值最高的前二十位主治医师 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findDocByCMIList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String stdId = paramMap == null?"":paramMap.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("stdId", stdId);
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer();
		sql.append(" select X.physician,X.P00300000, X.weight,X.drgCnt, X.cmi,X.time, X.fee, X.ratio, X.rate, X.incnt \n");
		sql.append(" from (select DISTINCT mr.P00600300 physician  ,mr.P00300000 ,SUM(A.AS_WEIGHT) weight,COUNT(DISTINCT p.PARDRG_CODE) drgCnt,ROUND(decode(COUNT(P.PARID),0,0,SUM(A.AS_WEIGHT)/COUNT(P.PARID)),2) cmi , \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,mr.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  time, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,mr.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) fee , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1' AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2' AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 rate, \n");
		sql.append(" count(p.PARID) incnt \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON mr.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and mr.P00600300 IS NOT NULL AND A.AS_ID=:stdId AND A.AS_WEIGHT IS not NULL  and p.ver_region=:groupScheme and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+" \n");
		sql.append(" GROUP BY mr.P00600300,mr.P00300000 ORDER BY cmi DESC  ) X \n");
		sql.append(" where X.incnt>=10 \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:附表5未入组病例覆盖的所有主要诊断 
	  * @param paramMap
	  * @return
	 */
	@Override
	public List<Object[]> findNotInGroupDiagList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer(); 
		sql.append(" SELECT X.PD0101, X.PD0100, X.cnt, ROUND(decode(Y.allcnt,0,0,X.cnt/Y.allcnt*100),2) \n");
		sql.append(" from (select de.PD0101, de.PD0100 ,count(1) cnt from drgs.MR_IPINFO m left join drgs.MR_DIAGNOSE de on m.PMAININDEX=de.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and de.pdtype='M' AND  m.P00320000<61 and p.PARDRG_CODE is null and p.ver_region=:groupScheme \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","m",2)+" \n");
		sql.append(" GROUP BY de.PD0101, de.PD0100 ORDER BY cnt DESC ) X, \n");
		sql.append(" (select count(1) allcnt from drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and m.P00320000<61 and p.PARDRG_CODE is null  \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","m",2)+" \n");
		sql.append(" and p.ver_region=:groupScheme )Y \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	 * @Description:附表6 未入组病例覆盖的所有主要手术/操作
	 * @param paramMap
	 * @return
	 */
	@Override
	public List<Object[]> findNotInGroupOperList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null?"":paramMap.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null?"":paramMap.get("reportDateTo").toString();
		String groupScheme = paramMap == null?"":paramMap.get("groupScheme").toString(); //获取分组方案
		String hospCode = paramMap == null?"":paramMap.get("hospCode").toString(); //获得院区信息
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",reportDateFrom+" 00:00:00" );
		map.put("dateTo", reportDateTo+" 23:59:59");
		map.put("groupScheme", groupScheme);
		map.put("hospCode", hospCode);
		StringBuffer sql=new StringBuffer(); 
		sql.append(" SELECT X.PO0101, X.PO0100, X.cnt, ROUND(decode(Y.allcnt,0,0,X.cnt/Y.allcnt*100),2) \n");
		sql.append(" from (select o.PO0101, o.PO0100 ,count(1) cnt from drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" LEFT JOIN drgs.mr_operation o on o.PMAININDEX=m.PMAININDEX \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and m.P00320000<61 and p.PARDRG_CODE is null and p.ver_region=:groupScheme and o.POTYPE='M' \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","m",2)+" \n");
		sql.append(" GROUP BY o.PO0101, o.PO0100 ORDER BY cnt DESC ) X, \n");
		sql.append(" (select count(1) allcnt from drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" where instr(:hospCode , BA.p00010001)>0 and m.P00320000<61 and p.PARDRG_CODE is null \n");
		sql.append(" "+Arith.mrOutDateSql("dateFrom","dateTo","m",2)+" \n");
		sql.append(" and p.ver_region=:groupScheme )Y \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	/**
	 * 
	  * @Description:校验数据完整性
	  * @param dateFrom
	  * @param dateTo
	  * @param benchmarkYear
	  * @param drgVersion
	  * @return
	 */
	@Override
	public List<Object[]> findCheckedData(String dateFrom, String dateTo,String hospCode) {
		Map<String, Object> map= new HashMap<String, Object>();
		map.put("dateFrom",dateFrom+" 00:00:00" );
		map.put("dateTo", dateTo+" 23:59:59");
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0301 is NOT null AND op.POTYPE='M' THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) , \n");
		sql.append(" decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0101 is NOT null AND op.POTYPE='M'  THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) , \n");
		sql.append(" decode(COUNT(op.PO0101),0,0,SUM(CASE WHEN op.PO0301 is not null  THEN 1 else 0 end)/COUNT(op.PO0101)) , \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PDTYPE is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) , \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PD0101 is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end))  \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.mr_operation op on mr.PMAININDEX=op.PMAININDEX \n");
		sql.append(" LEFT  JOIN  drgs.MR_DIAGNOSE  di on mr.PMAININDEX=di.PMAININDEX\n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrOutDateSql("dateFrom","dateTo","mr",2)+"  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).setParameters(map).list();
		return list;
	}
	
	/**
	 * 
	 *  @Description：获取mdc
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.WordDRGsReportApplication#findMDC(java.util.Map)
	 */
	@Override
	public List<Object[]> findMDC(String groupScheme) {
		 String sql="select m.MDC_CODE,m.MDC_DESC from drgs.t_mdcgroup m   ";
		 sql+=" left join drgs.T_PATIENTDRG p on p.VER_CODE=m.VER_CODE and p.ver_region='"+groupScheme+"' ";
		 sql+=" GROUP BY m.MDC_CODE,m.MDC_DESC ";
		 List<Object[]> list = getRepository().createSqlQuery(sql).list();
		 return list;
	}
	/**
	 * 
	 *  @Description：获取重点专业基础信息
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.WordDRGsReportApplication#findMDCFirstOne(java.util.Map)
	 */
	@Override
	public List<Object[]> findMDCFirstOne(Map<String, Object> dto,String groupScheme) { 
		 String mdcCode=dto.get("mdcCode")+"";
		 String dateFrom=dto.get("dateFrom")+"";
		 String dateTo=dto.get("dateTo")+"";
	
		String sql="select y.totalcasecnt,x.MDCCASECNT,z.REALDRGSCNT, x.DRGSCNT, \n"
				+ " case WHEN  y.totalcasecnt<>0 THEN ROUND(x.MDCCASECNT/y.totalcasecnt*100,2) ELSE 0 end as caseratio,\n"
				+ "case WHEN  z.REALDRGSCNT<>0 THEN ROUND(x.DRGSCNT/z.REALDRGSCNT*100,2) ELSE 0 end as drgsratio from (\n"
				+ "SELECT COUNT(mr.PMAININDEX) AS MDCCASECNT,count(DISTINCT p.PARDRG_CODE) DRGSCNT \n"
				+ "from drgs.MR_IPINFO mr \n"
				+ "LEFT JOIN drgs.t_patientdrg p on  mr.PMAININDEX=p.parid \n"
				+ "WHERE p.MDC='"+mdcCode+"' AND P.ver_region='"+groupScheme+"' \n"
				+ ""+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+" \n"
				+ ")x,(SELECT COUNT(mr.PMAININDEX) TOTALCASECNT \n"
				+ "from drgs.MR_IPINFO mr \n"
				+ "LEFT JOIN drgs.t_patientdrg p on  mr.PMAININDEX=p.parid \n"
				+ "WHERE  P.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not NULL\n"
				+ ""+Arith.mrOutDateSql(dateFrom,dateTo,"mr",1)+")y,\n"
				+ "(SELECT COUNT(DISTINCT A.DRG_CODE) AS REALDRGSCNT FROM drgs.t_drgs a\n"
				+ "WHERE CONCAT('MDC',substr(a.DRG_CODE,1,1)) ='"+mdcCode+"' AND \n"
				+ "a.VER_CODE=(SELECT DISTINCT b.VER_CODE FROM drgs.t_patientdrg b \n"
				+ "where b.ver_region='"+groupScheme+"' ) )z";
		 List<Object[]> list = getRepository().createSqlQuery(sql).list();
		 return list;
	}
	/**  
	 * @Description: 根据分组方案获取分组器版本号
	 * @author shaozhiyuan 
	 * @date 2018年10月11日  
	 * @version V3.1.1
	 */  
	@Override
	public String getDRGversionCode(String groupScheme) {
		String drgsVersion="";
		String sql=" SELECT distinct p.ver_code from  drgs.t_patientdrg p where p.ver_region= '"+groupScheme+"' ";
		List<String> list = getRepository().createSqlQuery(sql).list();
		if(list.size()>0){
			drgsVersion=list.get(0).toString();
		}
		return drgsVersion;
	}
}
